﻿/*************************************************************************************
 *
 * 文 件 名:   DataBaseInit
 * 描    述:   表信息
 * 
 * 版    本：  V1.0
 * 创 建 者：  YSW 
 * 创建时间：  2021-09-15 9:47:51
 * ======================================
 * 历史更新记录
 * 版本：V          修改时间：         修改人：
 * 修改内容：
 * ======================================
*************************************************************************************/

using CommonModel.Field;
using CommonModel.Util;
using GXRTBTC.PublicClassLibrary.Field;
using System;
using System.Collections.Generic;

namespace GXRTBTC.DataStorageService.Model
{
    /// <summary>
    /// 表的相关描述 均基于涛思数据库
    /// </summary>
    public class TableInfo
    {
        /// <summary>
        /// 超级表
        /// 表名称 表名最大长度为 192
        /// </summary>
        public string stableName {
            get
            {
                string tmpName = tableCfg.file_name;
                return "cj_" + tmpName.ToLower().Substring(0, tmpName.LastIndexOf('.')).Replace(".", "_");
            }
        }
        /// <summary>
        /// 超级表所属所有子表
        /// </summary>
        public Dictionary<string, int> tableNames { get; set; } = null;
        /// <summary>
        /// 动态字段及其当前最大值
        /// </summary>
        public Dictionary<string, string> dynamic { get; set; } = null;
        /// <summary>
        /// 当前的最大层级数量
        /// </summary>
        public int layerNum { get; set; } = 3;
        /// <summary>
        /// 列，数据库应该有的
        /// 列的最大个数为1024，最小个数为2
        /// </summary>
        public Dictionary<int, ColumnInfo> columns { get; set; } = null;
        /// <summary>
        /// 写redis缓存时用
        /// </summary>
        public Dictionary<int, ColumnInfo> columnsCache { get; set; } = null;
        /// <summary>
        /// 全列模式时使用
        /// 字段顺序是从数据库获取的，确保全列模式不出现错误
        /// </summary>
        public List<string> allCols { get; set; } = new List<string>();
        /// <summary>
        /// 用于数据库保存是字段拼接
        /// 指定列模式插入数据时使用
        /// </summary>
        public string columnsStr { get; set; } = "";
        /// <summary>
        /// 0-taos,1-pgsql
        /// 默认0，pgsql使用时动态赋值1
        /// </summary>
        private int _dbType { get; set; } = 0;
        public int dbType
        {
            get 
            { 
                return _dbType;
            }
            set
            {
                _dbType = value;
                Init();
            }
        }
        /// <summary>
        /// 默认字段
        /// </summary>
        public Dictionary<int, ColumnInfo> columnsDft { get; set; } = null;
        /// <summary>
        /// 从数据库读取的字段配置明细
        /// </summary>
        public SchemaDocument tableCfg { get; set; } = null;

        public TableInfo(SchemaDocument tableCfg)
        {
            this.tableCfg = tableCfg;
            Init();
        }

        /// <summary>
        /// 初始化
        /// </summary>
        public void Init()
        {
            tableNames = new Dictionary<string, int>();
            columns = new Dictionary<int, ColumnInfo>();
            columnsCache = new Dictionary<int, ColumnInfo>();
            if (dbType == 0)
            {
                columnsDft = new Dictionary<int, ColumnInfo>()
                {
                    { 0, new ColumnInfo(){ columnNameDb = "ts", columnName = "ts", typeCfg = "TIMESTAMP", dbType = dbType} },   //涛思数据库，固定必须包含要给TIMESTAMP字段ts
                    { 1, new ColumnInfo(){ columnNameDb = "c_from", columnName = "c_from", columnLen = 100, typeCfg = "BINARY", dbType = dbType} },   //ip:port
                    { 2, new ColumnInfo(){ columnNameDb = "c_paramId", columnName = "c_paramId", columnLen = 11, typeCfg = "UINT32", dbType = dbType} },   //mysql表id，带上该配置，便于后续数据处理
                    { 3, new ColumnInfo(){ columnNameDb = "schema_name", columnName = "schema_name", columnLen = 100, typeCfg = "BINARY", dbType = dbType} },   //解析时获取的获取解析协议配置名称，便于后续分类查询等处理
                    { 4, new ColumnInfo(){ columnNameDb = "station_code", columnName = "station_code", columnLen = 30, typeCfg = "BINARY", dbType = dbType} },   //台站编码
                    { 5, new ColumnInfo(){ columnNameDb = "device_type", columnName = "device_type", columnLen = 10, typeCfg = "BINARY", dbType = dbType} },   //设备地址
                    { 6, new ColumnInfo(){ columnNameDb = "sevice_sn", columnName = "sevice_sn", columnLen = 50, typeCfg = "BINARY", dbType = dbType} }   //设备序列号 自定义的，预留用
                };
            }
            else
            {
                columnsDft = new Dictionary<int, ColumnInfo>()
                {
                    { 0, new ColumnInfo(){ columnNameDb = "ts", columnName = "ts", typeCfg = "TIMESTAMPTZ", dbType = dbType} },   //涛思数据库，固定必须包含要给TIMESTAMP字段ts
                    { 1, new ColumnInfo(){ columnNameDb = "c_from", columnName = "c_from", columnLen = 100, typeCfg = "VARCHAR", dbType = dbType} },   //ip:port
                    { 2, new ColumnInfo(){ columnNameDb = "c_paramId", columnName = "c_paramId", columnLen = 11, typeCfg = "UINT32", dbType = dbType} },   //mysql表id，带上该配置，便于后续数据处理
                    { 3, new ColumnInfo(){ columnNameDb = "schema_name", columnName = "schema_name", columnLen = 100, typeCfg = "VARCHAR", dbType = dbType} },   //解析时获取的获取解析协议配置名称，便于后续分类查询等处理
                    { 4, new ColumnInfo(){ columnNameDb = "station_code", columnName = "station_code", columnLen = 30, typeCfg = "VARCHAR", dbType = dbType} },   //台站编码
                    { 5, new ColumnInfo(){ columnNameDb = "i_station_code", columnName = "i_station_code", columnLen = 30, typeCfg = "INT", dbType = dbType} },   //台站编码
                    { 6, new ColumnInfo(){ columnNameDb = "device_type", columnName = "device_type", columnLen = 10, typeCfg = "VARCHAR", dbType = dbType} },   //设备地址
                    { 7, new ColumnInfo(){ columnNameDb = "sevice_sn", columnName = "sevice_sn", columnLen = 50, typeCfg = "VARCHAR", dbType = dbType} },   //设备序列号 自定义的，预留用
                    { 8, new ColumnInfo(){ columnNameDb = "position_id", columnName = "position_id", columnLen = 50, typeCfg = "VARCHAR", dbType = dbType} }   //工位号
                };
            }
            
            dynamic = tableCfg.dynamicParaDictionary;
            SetColumns();
        }

        /// <summary>
        /// 设置字段
        /// </summary>
        private void SetColumns()
        {
            Dictionary<int, SchemaDictionary> dicts = tableCfg.fieldDictionary;
            foreach (int key in dicts.Keys)
            {
                SetColumn(dicts[key], columns);
            }

            columnsStr = "";
            foreach (int key in columnsDft.Keys)
            {
                columnsStr = columnsStr + ("".Equals(columnsStr) ? "" : ",") + columnsDft[key].columnNameDb;
            }
            foreach (int key in columns.Keys)
            {
                if (!columns[key].isParent)
                {
                    columnsStr = columnsStr + ("".Equals(columnsStr) ? "" : ",") + columns[key].columnNameDb;
                }
            }
        }

        /// <summary>
        /// 设置字段明细
        /// 列名（字段）最大长度为64
        /// </summary>
        /// <param name="obj">行对象</param>
        /// <param name="columns">需要填充的行信息</param>
        /// <param name="suffix">字段后缀，loop时，避免字段重复，且容易区分</param>
        /// <param name="loopIdx">loop编号，写数据库时用</param>
        /// <param name="parentLoop">父层级的loop值</param>
        private void SetColumn(SchemaDictionary field, Dictionary<int, ColumnInfo> columns, string suffix = "", int loopIdx = -1, string parentLoop = "")
        {
            if (!field.isNode)
            {
                if ("1".Equals(field.isSave))
                {
                    //字段长度最大64，如果超过，创建表时会自动截取掉
                    //string columnName = "c_" + field.getName() + field.getIndex() + ("".Equals(suffix) ? suffix : "_" + suffix);
                    //string columnName = field.getName() + ("".Equals(suffix) ? suffix : "_" + suffix);
                    string columnName = field.name + suffix;
                    columns.Add(columns.Count, new ColumnInfo() { parentLoop = parentLoop, dbType = dbType, loopIdx = loopIdx, layer = field.layer, field = field, columnName = field.name, columnNameDb = (columnName.Length > 64) ? columnName.Substring(0, 64) : columnName, columnLen = field.size, typeCfg = field.type.ToString() });
                }
            }
            else
            {
                if ("1".Equals(field.isSave))
                { 
                    Dictionary<int, SchemaDictionary> dicts = field.fieldDictionary;

                    //改成一个字段用分隔符分开（字段长度加长一些），不用多个字段，在动态loop的时候容易出问题
                    //父节点isParent为true
                    //字段长度最大64，如果超过，创建表时会自动截取掉
                    //string columnName = "c_" + field.getName() + field.getIndex() + ("".Equals(suffix) ? suffix : "_" + suffix);
                    //string columnName = field.getName() + ("".Equals(suffix) ? suffix : "_" + suffix);
                    string columnName = field.name + suffix;
                    columns.Add(columns.Count, new ColumnInfo() { parentLoop = parentLoop, dbType = dbType, loopIdx = loopIdx, layer = field.layer, isParent = true, field = field, columnName = field.name, columnNameDb = (columnName.Length > 64) ? columnName.Substring(0, 64) : columnName, columnLen = field.size, typeCfg = field.type.ToString() });

                    int loopThis = 1;   //循环
                    string loopStr = field.loop;
                    //如果是数值，则直接读取
                    if (TransForm.IsInt(loopStr))
                    {
                        loopThis = int.Parse(loopStr);
                    }
                    //如果是动态字段，则读取其默认（当前最大）值
                    else if(dynamic.ContainsKey(loopStr))
                    {
                        loopThis = int.Parse(dynamic[loopStr]);
                    }
                    for (int i = 0; i < loopThis; i++)
                    {
                        //string suffixNext = field.getName() + field.getIndex() + "_" + i.ToString() + ("".Equals(suffix) ? "" : "_" + suffix);
                        //string suffixNext = i.ToString() + field.getName() + ("".Equals(suffix) ? "" : "_" + suffix);
                        string suffixNext = "_" + i.ToString() + "_" + field.name + suffix;
                        foreach (int keyIn in dicts.Keys)
                        {
                            SetColumn(dicts[keyIn], columns, suffixNext, i, loopStr);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 获取创建表（以超级表为模板创建数据表）语句
        /// 涛思专用
        /// </summary>
        /// <param name="tbName"></param>
        /// <param name="positionId"></param>
        /// <returns></returns>
        public string GetCreateTableSql(string tbName, string positionId, string subcenter_id, string station_level)
        {
            string[] positionInfos = positionId.Split('_');
            string station_id = positionInfos[0];
            string device_type = positionInfos.Length > 1 ? positionInfos[1] : "";
            string device_no = positionInfos.Length > 2 ? positionInfos[2] : "1";
            return "CREATE TABLE IF NOT EXISTS " + tbName + " USING " + stableName + " (t_position_id, t_station_id, t_device_type, t_device_no, t_subcenter_id, t_station_level) " +
                "TAGS ('" + positionId + "','" + station_id + "','" + device_type + "'," + device_no + ",'" + subcenter_id + "'," + station_level + ")";
        }

        /// <summary>
        /// 获取创建超级表语句
        /// 涛思专用
        /// </summary>
        /// <returns></returns>
        public string GetCreateSTableSql()
        {
            string colsStr = "";

            //加上默认字段
            foreach (int key in columnsDft.Keys)
            {
                colsStr = colsStr + ("".Equals(colsStr) ? "" : ",") + columnsDft[key].columnNameDb + " " +
                  columnsDft[key].columnType + (("BINARY".Equals(columnsDft[key].columnType) || "VARCHAR".Equals(columnsDft[key].columnType) || "NCHAR".Equals(columnsDft[key].columnType)) ? "(" + columnsDft[key].columnLen.ToString() + ")" : "");
            }

            //20210527屏蔽 创建表语句太长了，至少再本地docker里面都是执行超时或失败的
            //数据字段
            //表的每行长度不能超过 16k 个字符
            foreach (int key in columns.Keys)
            {
                if (!columns[key].isParent)
                {
                    //使用数据类型 binary 或 nchar，需指定其最长的字节数
                    //字段长度建议不超过2000
                    colsStr = colsStr + ("".Equals(colsStr) ? "" : ",") + columns[key].columnNameDb + " " +
                      columns[key].columnType + (("BINARY".Equals(columns[key].columnType) || "VARCHAR".Equals(columns[key].columnType) || "NCHAR".Equals(columns[key].columnType)) ? "(" + columns[key].columnLen.ToString() + ")" : "");
                }
            }

            //超级表
            //20220929 ysw 根据阿池意见，为了方便统计，保留position_id，新增station_id、device_type、device_no三个tag，以及reserve1、reserve2两个预留tag
            //添加t_前缀，避免和表字段重复
            return "CREATE STABLE IF NOT EXISTS " + stableName + "(" + colsStr + ") TAGS(t_position_id NCHAR(25), t_station_id BINARY(15), t_device_type BINARY(8), t_device_no INT, t_subcenter_id BINARY(15), t_station_level int, t_reserve1 nchar(10), t_reserve2 INT)";
        }

        /// <summary>
        /// 获取创建超级表语句
        /// pgsql普通表
        /// </summary>
        /// <returns></returns>
        public string GetCreateTableSql()
        {
            string colsStr = "";

            //加上默认字段
            foreach (int key in columnsDft.Keys)
            {
                colsStr = colsStr + ("".Equals(colsStr) ? "" : ",") + columnsDft[key].columnNameDb + " " +
                  columnsDft[key].columnType + ("VARCHAR".Equals(columnsDft[key].columnType) ? "(" + columnsDft[key].columnLen.ToString() + ")" : "");
            }

            //数据字段
            //表的每行长度不能超过 16k 个字符
            foreach (int key in columns.Keys)
            {
                if (!columns[key].isParent)
                {
                    //使用数据类型 binary 或 nchar，需指定其最长的字节数
                    //字段长度建议不超过2000
                    colsStr = colsStr + ("".Equals(colsStr) ? "" : ",") + columns[key].columnNameDb + " " +
                      columns[key].columnType + ("VARCHAR".Equals(columns[key].columnType) ? "(" + columns[key].columnLen.ToString() + ")" : "");
                }
            }

            //超级表
            //添加t_前缀，避免和表字段重复
            return "CREATE TABLE IF NOT EXISTS " + stableName + "(" + colsStr + ")";
        }

        /// <summary>
        /// pgsql普通表转超表语法
        /// </summary>
        /// <returns></returns>
        public string GetHypertableSql()
        {
            //将普通表转换为时序表（对时间进行分区）
            return "select create_hypertable('" + stableName.ToLower() + "','ts')";
        }

        /// <summary>
        /// pgsql普通表
        /// 其他语句集合
        /// </summary>
        /// <returns></returns>
        public List<string> GetOtherSqls()
        {
            //先尝试一下，后续根据需求再修改
            List<string> sqls = new List<string>();
            //确定主键
            //sqls.Add("ALTER TABLE " + stableName.ToLower() + " ADD CONSTRAINT " + stableName.ToLower() + "_pkey PRIMARY KEY (ts)");
            //采用 add_dimension直接进行添加，number_partitions为空间分区的个数，必须指定
            //这里根据台站编号station_code划分分区
            sqls.Add("SELECT add_dimension('" + stableName.ToLower() + "', 'i_station_code', number_partitions => 1000)");
            //索引 工位号、台站编号、设备类型
            sqls.Add("CREATE INDEX ON " + stableName.ToLower() + "(position_id)");
            sqls.Add("CREATE INDEX ON " + stableName.ToLower() + "(station_code)");
            sqls.Add("CREATE INDEX ON " + stableName.ToLower() + "(device_type)");
            return sqls;
        }
    }
}
